﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

/// <summary>
/// Summary description for DayNightDAL
/// </summary>
public class DayNightDAL:IDayNightDAL
{

    public DayNightDAL()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public List<DayNight> GetAll()
    {
        string strSQL = @"SELECT
	                        [DayNightID],
	                        [Name],
	                        [FromTime],
	                        [ToTime],
	                        [LastUser]
                        FROM
	                        [DayNight] ORDER BY [DayNightID] ASC";
        SqlDataReader reader;
        List<DayNight> list = null;
        DayNight dayNight;
        try
        {
            reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
            if (reader.HasRows)
            {
                list = new List<DayNight>();
                while (reader.Read())
                {
                    dayNight = new DayNight();
                    dayNight.Id = Boolean.Parse(reader["DayNightID"].ToString());
                    dayNight.Name = Constants.SPACE + reader["Name"].ToString();
                    dayNight.FromTime = DateTime.Parse(reader["FromTime"].ToString());
                    dayNight.ToTime = DateTime.Parse(reader["ToTime"].ToString());
                    dayNight.LastUser = new Guid(reader["LastUser"].ToString());
                    list.Add(dayNight);
                }
            }

            return list;
        }
        catch (Exception ex)
        {
            throw ex;
        }

    }

    /// <summary>
    /// Delete
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public bool Save(List<DayNight> list)
    {
        bool flag = false;
        string strSQLDelete = "DELETE FROM [DayNight]";
        string strSQLIsert = @"INSERT INTO [DayNight] (
		                            [DayNightID],
		                            [Name],
		                            [FromTime],
		                            [ToTime],
		                            [LastUser]
	                            )VALUES (
		                            @DayNightID,
		                            @Name,
		                            @FromTime,
		                            @ToTime,
		                            @LastUser)";
        string strSQLUpdate = @"UPDATE DAYNIGHT SET 
	                                FromTime=@FromTime
	                                ,ToTime=@ToTime
	                                ,LastUser=@LastUser
	                                ,LastUpdate=GETDATE() WHERE DayNightID=@DayNightID";
        SqlTransaction trans = null;
        try
        {
            //sql connect
            SqlConnection conn = SqlHelper.Connection;
            conn.Open();
            trans = conn.BeginTransaction();
           
            //DELETE
            //SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSQLDelete);

            //Insert
            foreach (DayNight dayNight in list)
            {
               
                //parms
                SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@DayNightID",dayNight.Id),
                        new SqlParameter("@FromTime",dayNight.FromTime),
                        new SqlParameter("@ToTime",dayNight.ToTime),
                        new SqlParameter("@LastUser",dayNight.LastUser)
                    };

                //excute
                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, strSQLUpdate, parms);
            }
            trans.Commit();
            flag = true;
        }
        catch (Exception)
        {
            trans.Rollback();
            throw;
        }
        return flag;
    }

   
}